from database import get_db_client, get_query_helper, execute_sql, execute_sql_to_df
from datetime import datetime, timedelta


def example_basic_sql_query():
    """示例1: 基础SQL查询"""
    print("=== 基础SQL查询示例 ===")
    
    # 直接执行SQL查询
    sql = "SELECT COUNT(*) as total_logs FROM log"
    results = execute_sql(sql)
    print(f"工时日志总数: {results[0]['total_logs']}")
    
    # 带参数的查询
    sql = "SELECT * FROM framework WHERE FrameworkName = %s"
    results = execute_sql(sql, ("AGI部",))
    print(f"AGI部信息: {results}")


def example_dataframe_query():
    """示例2: 返回DataFrame的查询"""
    print("\n=== DataFrame查询示例 ===")
    
    # 查询最近的工时日志并转为DataFrame
    sql = """
    SELECT l.PeopleName, l.WorkHour, l.LogText, l.CreateTime,
           p.ProjectName
    FROM log l
    LEFT JOIN ProjectInf p ON l.ProjectDataId = p.Id
    ORDER BY l.CreateTime DESC
    LIMIT 10
    """
    
    df = execute_sql_to_df(sql)
    print(f"最近10条工时记录:")
    print(df.head())
    print(f"总工时: {df['WorkHour'].sum()}")


def example_business_queries():
    """示例3: 使用业务查询辅助类"""
    print("\n=== 业务查询示例 ===")
    
    helper = get_query_helper()
    
    # 查询指定员工的工时
    start_date = "2025-01-01"
    end_date = "2025-01-11"
    
    # 假设查询张三的工时
    people_logs = helper.get_people_worklogs("郭特", start_date, end_date)
    print(f"郭特在{start_date}到{end_date}期间的工时记录数: {len(people_logs)}")
    
    # 计算总工时
    total_hours = helper.get_people_total_hours("郭特", start_date, end_date)
    print(f"郭特总工时: {total_hours} 小时")
    
    # 获取所有员工列表
    all_people = helper.get_all_people()
    print(f"员工总数: {len(all_people)}")
    
    # 获取所有部门
    all_frameworks = helper.get_all_frameworks()
    print(f"部门总数: {len(all_frameworks)}")


def example_comprehensive_query():
    """示例4: 综合查询"""
    print("\n=== 综合查询示例 ===")
    
    helper = get_query_helper()
    
    # 多条件组合查询
    results = helper.get_comprehensive_report(
        framework_name="AGI部",
        start_date="2025-01-01",
        end_date="2025-01-31"
    )
    
    print(f"AGI部2025年1月工时记录数: {len(results)}")
    
    if results:
        # 分析数据
        total_hours = sum(float(r.get('WorkHour', 0)) for r in results)
        unique_people = {r['PeopleName'] for r in results}
        unique_projects = {r['ProjectName'] for r in results if r.get('ProjectName')}
        
        print(f"总工时: {total_hours} 小时")
        print(f"参与人员: {len(unique_people)} 人")
        print(f"涉及项目: {len(unique_projects)} 个")


def example_table_inspection():
    """示例5: 数据库表结构检查"""
    print("\n=== 表结构检查示例 ===")
    
    db_client = get_db_client()
    
    # 测试连接
    if db_client.test_connection():
        print("✅ 数据库连接正常")
    else:
        print("❌ 数据库连接失败")
        return
    
    # 获取所有表
    tables = db_client.get_all_tables()
    print(f"数据库表: {tables}")
    
    # 查看log表结构
    if 'log' in tables:
        log_structure = db_client.get_table_info('log')
        print("log表结构:")
        for field in log_structure:
            print(f"  {field['Field']}: {field['Type']} {'(主键)' if field['Key'] == 'PRI' else ''}")


def example_custom_report():
    """示例6: 自定义报告查询"""
    print("\n=== 自定义报告示例 ===")
    
    # 按部门统计工时
    sql = """
    SELECT 
        f.FrameworkName as 部门,
        COUNT(l.logid) as 日志数量,
        SUM(l.WorkHour) as 总工时,
        AVG(l.WorkHour) as 平均工时,
        COUNT(DISTINCT l.PeopleName) as 人员数量
    FROM log l
    LEFT JOIN Peopleinf pi ON l.PeopleName = pi.PeopleName
    LEFT JOIN framework f ON pi.FrameworkId = f.id
    WHERE DATE(l.CreateTime) >= %s
    GROUP BY f.FrameworkName
    ORDER BY 总工时 DESC
    """
    
    # 查询最近30天的数据
    thirty_days_ago = (datetime.now() - timedelta(days=30)).strftime('%Y-%m-%d')
    
    results = execute_sql(sql, (thirty_days_ago,))
    
    print("最近30天各部门工时统计:")
    print("-" * 60)
    for row in results:
        if row.get('部门'):
            print(f"{row['部门']:10} | 工时: {float(row['总工时'] or 0):6.1f}h | 人员: {row['人员数量']}人 | 日志: {row['日志数量']}条")


if __name__ == "__main__":
    """运行所有示例"""
    try:
        example_basic_sql_query()
        example_dataframe_query()
        example_business_queries()
        example_comprehensive_query()
        example_table_inspection()
        example_custom_report()
        
        print("\n✅ 所有示例运行完成!")
        
    except Exception as e:
        print(f"\n❌ 示例运行出错: {e}")
        print("请检查数据库连接配置和环境变量") 